using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CoreLab.Oracle;

namespace Common.Utilities
{
    /// <summary>
    /// 
    /// </summary>
    public class TableHelper
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="msg"></param>
        /// <param name="percent"></param>
        public delegate void SaveTableDataStatusChanged(string msg, int percent);
        /// <summary>
        /// 
        /// </summary>
        /// <param name="totalRecordsSaved"></param>
        public delegate void SaveTableDataFinished(int totalRecordsSaved);
        /// <summary>
        /// 
        /// </summary>
        /// <param name="error"></param>
        public delegate void SaveTableDataErred(string error);

        /// <summary>
        /// 
        /// </summary>
        public static event SaveTableDataErred OnSaveTableDataErred;

        /// <summary>
        /// 
        /// </summary>
        public static event SaveTableDataFinished OnSaveTableDataFinished;

        /// <summary>
        /// 
        /// </summary>
        public static event SaveTableDataStatusChanged OnSaveTableDataStatusChanged;

        /// <summary>
        /// drop table
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tblName"></param>
        public static void DropTable(SqlConnection conn, string tblName)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = " +
                "OBJECT_ID(N'[dbo].[" + tblName + "]') AND type in (N'U'))" +
                "DROP TABLE [dbo].[" + tblName + "]";
            // cmd.CommandText = "drop table [" + tblName + "]";
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// drop table
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        public static void DropTable(string connStr, string tblName)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = " +
                    "OBJECT_ID(N'[dbo].[" + tblName + "]') AND type in (N'U'))" +
                    "DROP TABLE [dbo].[" + tblName + "]";
                // cmd.CommandText = "drop table [" + tblName + "]";
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// drop oracle table
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        public static void DropOracleTable(string connStr, string tblName)
        {
            OracleConnection conn = new OracleConnection(connStr);
            try
            {
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = "drop table " + tblName;
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// truncate table data
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        public static void PurgeOracleTableData(string connStr, string tblName)
        {
            OracleConnection conn = new OracleConnection(connStr);
            try
            {
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = "truncate table " + tblName;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// create table
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tblName"></param>
        /// <param name="cols"></param>
        /// <param name="overwrite"></param>
        public static void CreateTable(SqlConnection conn, string tblName, TableColumn[] cols, bool overwrite)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand cmd = conn.CreateCommand();
            if (overwrite)
            {
                cmd.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = " +
                    "OBJECT_ID(N'[dbo].[" + tblName + "]') AND type in (N'U'))" +
                    "DROP TABLE [dbo].[" + tblName + "]";
                cmd.ExecuteNonQuery();
            }
            else
            {
                cmd.CommandText = "SELECT * FROM sys.objects WHERE object_id = " +
                    "OBJECT_ID(N'[dbo].[" + tblName + "]') AND type in (N'U')";
                SqlDataReader reader = cmd.ExecuteReader();
                bool exist = false;
                if (reader.Read())
                {
                    exist = true;
                }
                reader.Close();
                if (exist)
                {
                    return;
                }
            }
            if (cols != null && cols.Length > 0 && (!string.IsNullOrEmpty(tblName)))
            {

                string createSql1 = "create table [" + tblName + "] ";
                TableColumn pkField = new TableColumn();
                for (int i = 0; i < cols.Length; i++)
                {
                    if (cols[i].IsPK)
                    {
                        pkField = cols[i];
                        break;
                    }
                }
                string createSql2 = "(";
                if (!string.IsNullOrEmpty(pkField.ColumnName))
                {
                    if (pkField.IsIdentity)
                    {
                        createSql2 += "[" + pkField.ColumnName + "] " +
                                      FieldDataType.ToMssqlFieldDataTypeString(
                                          pkField.GetFieldDbType(DataProviderType.MSSQL)) + " identity primary key,";
                    }
                    else
                    {
                        createSql2 += "[" + pkField.ColumnName + "] " +
                                      FieldDataType.ToMssqlFieldDataTypeString(
                                          pkField.GetFieldDbType(DataProviderType.MSSQL)) + " primary key,";
                    }
                }
                for (int i = 0; i < cols.Length; i++)
                {
                    if (!cols[i].IsPK)
                    {
                        createSql2 += "[" + cols[i].ColumnName + "] " +
                            FieldDataType.ToMssqlFieldDataTypeString(cols[i].GetFieldDbType(DataProviderType.MSSQL));
                        if (!cols[i].AllowNull)
                        {
                            createSql2 += " not null";
                        }
                        createSql2 += ",";
                    }
                }
                if(createSql2.EndsWith(","))
                {
                    createSql2 = createSql2.Substring(0, createSql2.Length - 1);
                }
                createSql2 += ")";
                cmd.CommandText = createSql1 + createSql2;
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// create MSSQL table based on col info
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="cols"></param>
        /// <param name="overwrite"></param>
        public static void CreateTable(string connStr, string tblName, TableColumn[] cols, bool overwrite)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand cmd = conn.CreateCommand();
                if (overwrite)
                {
                    cmd.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = " +
                        "OBJECT_ID(N'[dbo].[" + tblName + "]') AND type in (N'U'))" +
                        "DROP TABLE [dbo].[" + tblName + "]";
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM sys.objects WHERE object_id = " +
                        "OBJECT_ID(N'[dbo].[" + tblName + "]') AND type in (N'U')";
                    SqlDataReader reader = cmd.ExecuteReader();
                    bool exist = false;
                    if (reader.Read())
                    {
                        exist = true;
                    }
                    reader.Close();
                    if (exist)
                    {
                        return;
                    }
                }
                if (cols != null && cols.Length > 0 && (!string.IsNullOrEmpty(tblName)))
                {

                    string createSql1 = "create table [" + tblName + "] ";
                    Dictionary<string, TableColumn> pkFields = new Dictionary<string, TableColumn>();
                    for (int i = 0; i < cols.Length; i++)
                    {
                        if (cols[i].IsPK)
                        {
                            pkFields.Add(cols[i].ColumnName, cols[i]);
                        }
                    }
                    string createSql2 = "(";
                    //if (!string.IsNullOrEmpty(pkField.ColumnName))
                    //{
                    //    if (pkField.IsIdentity)
                    //    {
                    //        createSql2 += "[" + pkField.ColumnName + "] " +
                    //                      FieldDataType.ToMssqlFieldDataTypeString(
                    //                          pkField.GetFieldDbType(DataProviderType.MSSQL)) +
                    //                      " identity primary key,";
                    //    }
                    //    else
                    //    {
                    //        createSql2 += "[" + pkField.ColumnName + "] " +
                    //                      FieldDataType.ToMssqlFieldDataTypeString(
                    //                          pkField.GetFieldDbType(DataProviderType.MSSQL)) +
                    //                      " primary key,";
                    //    }
                    //}
                    for (int i = 0; i < cols.Length; i++)
                    {
                        //if (!cols[i].IsPK)
                        {
                            createSql2 += "[" + cols[i].ColumnName + "] " +
                                FieldDataType.ToMssqlFieldDataTypeString(cols[i].GetFieldDbType(DataProviderType.MSSQL));
                            if (!cols[i].AllowNull)
                            {
                                createSql2 += " not null";
                            }
                            if (i < cols.Length - 1)
                                createSql2 += ",";
                        }
                    }
                    
                    if(pkFields.Count>0)
                    {
                        createSql2 += ", constraint [pk_" + tblName + "] primary key clustered (";
                        string[] pkFieldNames = new string[pkFields.Count];
                        pkFields.Keys.CopyTo(pkFieldNames, 0);
                        for(int i=0;i<pkFieldNames.Length;i++)
                        {
                            createSql2 += pkFieldNames[i] + " ASC";
                            if (i < pkFieldNames.Length - 1)
                                createSql2 += ",";
                        }
                        createSql2 += ")";
                    }
                    createSql2 += ")";
                    cmd.CommandText = createSql1 + createSql2;
                    cmd.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="cols"></param>
        /// <param name="overwrite"></param>
        public static void CreateTable(string connStr, string schemaName, string tblName, TableColumn[] cols, bool overwrite)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand cmd = conn.CreateCommand();
                if (overwrite)
                {
                    cmd.CommandText = "IF EXISTS (SELECT * FROM sys.all_objects WHERE name = '" +
                                      tblName + "' AND type ='U' and schema_id in (" +
                                      "select schema_id from sys.schemas where name='" + @schemaName + "'))" +
                                      "DROP TABLE [" + schemaName + "].[" + tblName + "]";
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM sys.all_objects WHERE name = '" +
                                      tblName + "' AND type ='U' and schema_id in (" +
                                      "select schema_id from sys.schemas where name='" + @schemaName + "')";
                    SqlDataReader reader = cmd.ExecuteReader();
                    bool exist = false;
                    if (reader.Read())
                    {
                        exist = true;
                    }
                    reader.Close();
                    if (exist)
                    {
                        return;
                    }
                }
                if (cols != null && cols.Length > 0 && (!string.IsNullOrEmpty(tblName)))
                {

                    string createSql1 = "create table [" + schemaName + "].[" + tblName + "] ";
                    Dictionary<string, TableColumn> pkFields = new Dictionary<string, TableColumn>();
                    for (int i = 0; i < cols.Length; i++)
                    {
                        if (cols[i].IsPK)
                        {
                            pkFields.Add(cols[i].ColumnName, cols[i]);
                        }
                    }
                    string createSql2 = "(";
                    
                    for (int i = 0; i < cols.Length; i++)
                    {
                        //if (!cols[i].IsPK)
                        {
                            createSql2 += "[" + cols[i].ColumnName + "] " +
                                FieldDataType.ToMssqlFieldDataTypeString(cols[i].GetFieldDbType(DataProviderType.MSSQL));
                            if (!cols[i].AllowNull)
                            {
                                createSql2 += " not null";
                            }
                            if (i < cols.Length - 1)
                                createSql2 += ",";
                        }
                    }

                    if (pkFields.Count > 0)
                    {
                        createSql2 += ", constraint [pk_" + tblName + "] primary key clustered (";
                        string[] pkFieldNames = new string[pkFields.Count];
                        pkFields.Keys.CopyTo(pkFieldNames, 0);
                        for (int i = 0; i < pkFieldNames.Length; i++)
                        {
                            createSql2 += pkFieldNames[i] + " ASC";
                            if (i < pkFieldNames.Length - 1)
                                createSql2 += ",";
                        }
                        createSql2 += ")";
                    }
                    createSql2 += ")";
                    cmd.CommandText = createSql1 + createSql2;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>
        /// create oracle table
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="cols"></param>
        public static void CreateOracleTable(string connStr, string tblName, TableColumn[] cols)
        {
            OracleConnection conn = new OracleConnection(connStr);
            try
            {
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = "create table " + tblName + " (";
                for (int i = 0; i < cols.Length; i++)
                {
                    cmd.CommandText += "\"" + cols[i].ColumnName + "\"";
                    FieldDataType fieldType = new FieldDataType();
                    fieldType.DataType = cols[i].DbType;
                    fieldType.Nullible = cols[i].AllowNull;
                    fieldType.Precision = cols[i].Precision;
                    fieldType.Provider = DataProviderType.Oracle;
                    fieldType.Scale = cols[i].Scale;
                    fieldType.Size = cols[i].Size;
                    string oracleDbTypeString = FieldDataType.ToOracleFieldDataTypeString(fieldType);
                    cmd.CommandText += " " + oracleDbTypeString;

                    if (i < cols.Length - 1)
                    {
                        cmd.CommandText += ",";
                    }
                    else
                    {
                        cmd.CommandText += ")";
                    }
                }
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// truncate table
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tblName"></param>
        public static void TruncateTable(SqlConnection conn, string tblName)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = " +
                "OBJECT_ID(N'[dbo].[" + tblName + "]') AND type in (N'U'))" +
                "truncate TABLE [dbo].[" + tblName + "]";
            // cmd.CommandText = "truncate table [" + tblName + "]";
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="dtData"></param>
        /// <param name="append"></param>
        public static void SaveTableToDb(string connStr, string tblName, DataTable dtData, bool append)
        {
            List<TableColumn> cols=new List<TableColumn>();
            Dictionary<string, SqlParameter> parameters = new Dictionary<string, SqlParameter>();
            foreach(DataColumn dataCol in dtData.Columns)
            {
                TableColumn col=new TableColumn();
                col.AllowNull = dataCol.AllowDBNull;
                col.ColumnName = dataCol.ColumnName;
                col.DbType = FieldDataType.ToDbType(dataCol.DataType.ToString());
                col.IsFK = false;
                col.IsIdentity = false;
                col.IsIndexed = false;
                col.IsPK = false;
                col.Ordinal = dataCol.Ordinal;
                col.Precision = 0;
                col.Scale = 0;
                col.Size = FieldDataType.GetFieldDefaultSize(col.DbType);
                cols.Add(col);

                SqlParameter sqlParam=new SqlParameter("@"+col.ColumnName, DBNull.Value);
                parameters.Add(col.ColumnName, sqlParam);
            }
            string spName = string.Format("insert_{0}_SP", tblName);
            string spBody =
                StoredProcUtil.CreateStoredProcedureStatementForInsert(
                    DataProviderType.MSSQL, spName,
                    string.Empty, tblName, cols.ToArray());

            TableHelper.CreateTable(connStr, tblName, cols.ToArray(), !append);
            DbUtil.NewStoredProc(connStr, spName, spBody);
            
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < dtData.Rows.Count; i++)
                {
                    cmd.Parameters.Clear();
                    for(int k=0;k<dtData.Columns.Count;k++)
                    {
                        object fieldValue = dtData.Rows[i][k];
                        SqlParameter sqlParam = parameters[dtData.Columns[k].ColumnName];
                        if(fieldValue==null)
                            sqlParam.Value = DBNull.Value;
                        else
                            sqlParam.Value = fieldValue;
                        cmd.Parameters.Add(sqlParam);
                    }
                    cmd.ExecuteNonQuery();

                    if(TableHelper.OnSaveTableDataStatusChanged !=null && (i+1) % 100==0)
                    {
                        string msg = string.Format("Saving data to table {0}... {1} of {2} records saved.", tblName,
                                                   (i + 1), dtData.Rows.Count);
                        int percent = (i + 1)*100/dtData.Rows.Count;
                        TableHelper.OnSaveTableDataStatusChanged(msg, percent);
                    }
                }

                if(TableHelper.OnSaveTableDataFinished !=null)
                {
                    TableHelper.OnSaveTableDataFinished(dtData.Rows.Count);
                }
            }
            catch (Exception ex)
            {
                if(TableHelper.OnSaveTableDataErred !=null)
                {
                    TableHelper.OnSaveTableDataErred(ex.ToString());
                }
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
